Custom Rollup fields

Microsoft introduced Rollup Fields to the Dynamics CRM platform in 2015, and this added significant positive functionality to the platform. Rollup fields contain aggregated values that have been calculated from child records for a parent record. Typical examples of this would be total value of Open Opportunities for an Account, or the total number of Emails received for a Contact.

Whilst there are benefits to Rollup Fields, there are also some “gotchas” to be aware of before you implement them in your environment. Jukka Niiranen has outlined some of these on his blog, and some of the other considerations are outlined on the Microsoft site. For me, the two main considerations are:

1. Rollup fields are asynchronous, so they don’t update in real-time
2. You can only have a maximum of 100 rollup fields across your organisation, and 10 per entity

With these drawbacks in mind, you may wish to explore alternatives to creating rollup fields.

The Scenario

In my environment, we define our Opportunities in two ways:

1. Is the Opportunity from a New Client, or is it Account Development with an existing Client?
2. Which Segment of our business is the Opportunity associated with?

In order to capture this information, we have a custom entity for the Segments with a lookup field on the Opportunity, and an Option Set field for the Opportunity Type.

For each Segment, we wanted to be able to see:

1. What is the total value of the Estimated Revenue of All Open Opportunities?
2. What is the total value of the Weighted Revenue of All Open Opportunities?
3. How many Open Opportunities are there?
4. What is the total value of the Estimated Revenue of all Open Account Development Opportunities ?
5. What is the total value of the Weighted Revenue of all Open Account Development Opportunities?
6. How many Account Development Open Opportunities are there?
7. What is the total value of the Estimated Revenue of all New Client Open Opportunities?
8. What is the total value of the Weighted Revenue of all New Client Open Opportunities?
9. How many New Client Open Opportunities are there?

This is easily achievable using standard Rollup Fields, however it would require 9 rollup fields which is a significant proportion of the available rollup fields on the entity and the organisation.

The Solution

One of my favourite things about the Dynamics community is the amount of free content that contributors have made available to help others solve problems. In this case, I’m going to be using the Dynamics 365 Workflow Tools created by Demian Raschkovan

In this set of Custom Workflow Activities there is an option called Rollup Functions that we will be using. This allows you to use a FetchXML query to define the records we’ll be summing up.

Setting up the Entity

On the entity that you’d like to add the “rollup” fields to (in this case the Segment entity), create Simple fields to capture the data you’ll be summarising. In my case, I have added six Currency fields (for the Estimated Revenue and Weighted Revenue calculations) and three Whole Number fields (for the Quantity calculations)

Creating the Workflow

Create a real-time workflow on the entity you’ll be summarising the data from (in this case the Opportunity entity), and set the trigger to run on Create, or on Update of the Segment, Estimated Revenue or Probability fields.

The first step is to get the GUID of the referenced Segment on the Opportunity, and Demian has a function for that too – Get Record ID. We need the GUID to reference in the FetchXML we’ll be using.

Note: in the description for the Rollup Functions activity it suggests you can use a {PARENT_GUID} tag to pass in dynamic data, but I couldn’t get it to work consistently, so I use the Get Record ID activity to bypass that requirement.

The next step is to add a Rollup Functions step to your workflow, and then define the FetchXML you’ll be using. There are two methods to get your FetchXML, either using the Advanced Find, or using the FetchXML Builder by Jonas Rapp.

I want to find all Open Opportunities against a specific segment so I add two conditions, and ensure the field I will be aggregating is first in the list, which gives me a FetchXML query that looks like this:

We’ll paste this into the Workflow Step properties for the Fetch XML and replace the GUID with the output from the Get Record ID step

We repeat this step for each FetchXML query we want to run. In my case, I’ve ended up with six Rollup Function steps in my workflow. The Rollup Functions can output the Average, Count, Max, Min or Sum of the query.

The final step of the Workflow is to update the Segment and update the values with the Rollup Function outputs

The workflow is now ready and will provide you with real-time rollup functions to count the values in your pipeline.

Updating the Previous Segment

Ah, what’s that I hear you say, we’re not done yet? Well, you’re right of course. The above is all well and good, but changing the Segment means the Opportunity is double-counted in the pipeline analysis, unless we change the previous segment too, so how do we do that?

You need to create a second lookup to the Segment entity on the Opportunity, to hold the Previous Segment. Then create another real-time workflow on the Opportunity that is triggered Before the Segment field changes, and copy the value in the Segment field to the Previous Segment, and then trigger a Child Workflow to update the Previous Segment. The Child Workflow will be a copy of the one we created above, however it will need to be a background workflow to ensure it captures the data correctly.

Notes

This offers a viable alternative to the OOTB rollup fields. It should be noted that using real-time workflows can add performance overhead to your environment, so consider what is best for your particular deployment. As ever, using third-party tools is at your own risk, so ensure it has been tested thoroughly before you consider deploying it.